﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;



using System.Data.SqlClient;

using Global.Class.Library;
using DataTransferObject;
using DataAccessLayer;
using DataAccessLayer.GCGBClippingDTSTableAdapters;


namespace BusinessLogicLayer
{
    public class GCGBClippingBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.GCGBClipping; } }

        private UserInformation userOrganization;

        private GCGBClippingMaster gcgbClippingMaster;

        private MyBindingList<GCGBClippingCollection> gcgbClippingCollectionList;

        private bool ReadonlyMode { get; set; }

        public GCGBClippingBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();

                userOrganization = new UserInformation();

                gcgbClippingMaster = new GCGBClippingMaster();

                this.gcgbClippingCollectionList = new MyBindingList<GCGBClippingCollection>();


                GlobalDefaultValue.Apply(this);


                this.GCGBClippingMaster.PropertyChanged += new PropertyChangedEventHandler(GCGBClippingMaster_PropertyChanged);
                this.gcgbClippingCollectionList.ListChanged += new ListChangedEventHandler(GCGBClippingCollection_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void GCGBClippingMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            if (!this.ReadonlyMode && (e.PropertyName == "GCGBCommandID" || e.PropertyName == "IsDraft"))
            {
                this.StopTracking();
                this.GCGBClippingGetDetail();
                this.StartTracking();
            }

            this.SetDirty();
        }

        private void GCGBClippingCollection_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>

        private GCGBClippingListingTableAdapter listingTableAdapter;
        protected GCGBClippingListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new GCGBClippingListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private GCGBClippingMasterTableAdapter masterTableAdapter;
        protected GCGBClippingMasterTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new GCGBClippingMasterTableAdapter();
                return masterTableAdapter;
            }
        }

        GCGBClippingCollectionTableAdapter collectionTableAdapter;
        protected GCGBClippingCollectionTableAdapter CollectionTableAdapter
        {
            get
            {
                if (collectionTableAdapter == null) collectionTableAdapter = new GCGBClippingCollectionTableAdapter();
                return collectionTableAdapter;
            }
        }


        GCGBClippingGetDetailTableAdapter getDetailTableAdapter;
        protected GCGBClippingGetDetailTableAdapter GetDetailTableAdapter
        {
            get
            {
                if (getDetailTableAdapter == null) getDetailTableAdapter = new GCGBClippingGetDetailTableAdapter();
                return getDetailTableAdapter;
            }
        }


        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public GCGBClippingDTS.GCGBClippingListingDataTable GCGBClippingListing(DateTime lowerFillterDate, DateTime upperFillterDate)
        {
            return this.ListingTableAdapter.GetData(lowerFillterDate, upperFillterDate);
        }

        public GCGBClippingMaster GCGBClippingMaster
        {
            get { return this.gcgbClippingMaster; }
        }

        public MyBindingList<GCGBClippingCollection> GCGBClippingCollectionList
        {
            get { return this.gcgbClippingCollectionList; }
        }


                
        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int GCGBClippingID   //Primary Key
        {
            get { return this.GCGBClippingMaster.GCGBClippingID; }
            private set
            {
                if (this.GCGBClippingMaster.GCGBClippingID != value)
                {
                    this.StopTracking();

                    this.GCGBClippingMaster.GCGBClippingID = value;

                    this.GCGBClippingGetMaster();
                    this.GCGBClippingGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        #endregion <Primary Key>

        private void GCGBClippingGetMaster()
        {
            if (this.GCGBClippingID > 0)
            {
                GCGBClippingDTS.GCGBClippingMasterDataTable masterDataTable = this.MasterTableAdapter.GetData(this.GCGBClippingID);

                if (masterDataTable.Count > 0)
                {
                    this.GCGBClippingMaster.StopTracking();

                    this.GCGBClippingMaster.GCGBCommandID = masterDataTable[0].GCGBCommandID;
                    this.GCGBClippingMaster.GCGBCommandReference = masterDataTable[0].GCGBCommandReference;
                    this.GCGBClippingMaster.GCGBCommandDate = masterDataTable[0].GCGBCommandDate;

                    this.GCGBClippingMaster.GCGBClippingDate = masterDataTable[0].GCGBClippingDate;
                    this.GCGBClippingMaster.EntryDate = masterDataTable[0].EntryDate;

                    this.GCGBClippingMaster.GCGBClippingReference = masterDataTable[0].GCGBClippingReference;
                    this.GCGBClippingMaster.Description = masterDataTable[0].Description;

                    this.GCGBClippingMaster.PreparedEmployeeID = masterDataTable[0].PreparedEmployeeID;
                    this.GCGBClippingMaster.ApprovedEmployeeID = masterDataTable[0].ApprovedEmployeeID;

                    this.GCGBClippingMaster.Remarks = masterDataTable[0].Remarks;

                    this.GCGBClippingMaster.IsDraft = masterDataTable[0].IsDraft;

                    this.GCGBClippingMaster.StartTracking();

                    this.GCGBClippingMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.GCGBClippingMaster);
                this.GCGBClippingMaster.GCGBClippingDate = DateTime.Today;
                this.GCGBClippingMaster.EntryDate = DateTime.Today;
                this.GCGBClippingMaster.Reset();
            }
        }


        private void GCGBClippingGetDetail()
        {
            this.gcgbClippingCollectionList.RaiseListChangedEvents = false;
            this.gcgbClippingCollectionList.Clear();
            if (this.GCGBClippingID >= 0)
            {
                if (this.GCGBClippingMaster.GCGBCommandID > 0)
                {
                    GCGBClippingDTS.GCGBClippingGetDetailDataTable gcgbClippingGetDetailDataTable = this.GetDetailTableAdapter.GetData(this.GCGBClippingID, this.GCGBClippingMaster.GCGBCommandID, this.ReadonlyMode, this.GCGBClippingMaster.IsDraft);

                    if (gcgbClippingGetDetailDataTable.Rows.Count > 0)
                    {
                        int gcgbCommandID = 0; int gcgbCommandSerialID = 0;
                        foreach (GCGBClippingDTS.GCGBClippingGetDetailRow gcgbClippingGetDetailRow in gcgbClippingGetDetailDataTable.Rows)
                        {
                            if (gcgbClippingGetDetailRow.RSC == 0 || gcgbCommandID != gcgbClippingGetDetailRow.GCGBCommandID || gcgbCommandSerialID != gcgbClippingGetDetailRow.GCGBCommandSerialID)
                            {
                                gcgbCommandID = gcgbClippingGetDetailRow.GCGBCommandID; gcgbCommandSerialID = gcgbClippingGetDetailRow.GCGBCommandSerialID;
                                this.gcgbClippingCollectionList.Add(new GCGBClippingCollection(gcgbClippingGetDetailRow.GCGBCommandID, gcgbClippingGetDetailRow.GCGBCommandSerialID, gcgbClippingGetDetailRow.GCGBCommandDate, gcgbClippingGetDetailRow.WorkerNameID, gcgbClippingGetDetailRow.MaterialSetID, gcgbClippingGetDetailRow.MaterialSemiID1, gcgbClippingGetDetailRow.QuantityPerSet1, gcgbClippingGetDetailRow.QuantitySemiBad1, gcgbClippingGetDetailRow.QuantitySemiLoss1, gcgbClippingGetDetailRow.QuantitySemiPreviousMinus1,
                                        gcgbClippingGetDetailRow.MaterialSemiID2, gcgbClippingGetDetailRow.QuantityPerSet2, gcgbClippingGetDetailRow.QuantitySemiBad2, gcgbClippingGetDetailRow.QuantitySemiLoss2, gcgbClippingGetDetailRow.QuantitySemiPreviousMinus2, gcgbClippingGetDetailRow.MaterialSemiID3, gcgbClippingGetDetailRow.QuantityPerSet3, gcgbClippingGetDetailRow.QuantitySemiBad3, gcgbClippingGetDetailRow.QuantitySemiLoss3, gcgbClippingGetDetailRow.QuantitySemiPreviousMinus3, gcgbClippingGetDetailRow.RemainsQuantity, gcgbClippingGetDetailRow.Quantity, gcgbClippingGetDetailRow.QuantityLoss, gcgbClippingGetDetailRow.Remarks));
                            }
                        }
                    }
                }
            }

            this.gcgbClippingCollectionList.RaiseListChangedEvents = true;
            this.gcgbClippingCollectionList.ResetBindings();
        }



        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<GCGBClippingCollection> inValidGCGBClippingCollection = this.GCGBClippingCollectionList.Where(gcgbClippingCollection => !gcgbClippingCollection.IsValid).ToList();
                return this.GCGBClippingMaster.IsValid && inValidGCGBClippingCollection.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (this.gcgbClippingMaster.GCGBClippingDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    if (!GlobalUserPermission.GetEditable("GCGBClippingApproved", this.GCGBClippingID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBClippingEditable", this.GCGBClippingID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (this.gcgbClippingMaster.GCGBClippingDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.GCGBClippingVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBClippingEditable", this.GCGBClippingID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (this.gcgbClippingMaster.GCGBClippingDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.GCGBClippingUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBClippingEditable", this.GCGBClippingID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int gcgbClippingID)
        {
            this.ReadonlyMode = true;
            if (this.GCGBClippingID == gcgbClippingID) this.GCGBClippingID = -1; //Enforce to reload
            this.GCGBClippingID = gcgbClippingID;
        }

        public void New()
        {
            this.ReadonlyMode = false;
            if (this.GCGBClippingID == 0) this.GCGBClippingID = -1;
            this.GCGBClippingID = 0;
        }

        public void Edit()
        {
            this.ReadonlyMode = false;
            int gcgbClippingID = this.GCGBClippingID;

            this.GCGBClippingID = -1;
            this.GCGBClippingID = gcgbClippingID;
        }

        #region Save & Delete Method


        public bool Save()
        {
            int gcgbClippingID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref gcgbClippingID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(gcgbClippingID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient save", "Save conflict");

                    transactionScope.Complete();                    
                }

                this.Fill(gcgbClippingID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.gcgbClippingMaster.GCGBClippingID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.gcgbClippingMaster.GCGBClippingID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.gcgbClippingMaster.GCGBClippingID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient delete", "Delete conflict");

                    transactionScope.Complete();                    
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.GCGBClippingMaster.PreparedEmployeeID, this.GCGBClippingMaster.GCGBClippingDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });


            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);
        }


        private bool SaveMaster(ref int gcgbClippingID)
        {
            GCGBClippingDTS.GCGBClippingMasterDataTable masterDataTable;
            GCGBClippingDTS.GCGBClippingMasterRow masterRow;

            if (this.gcgbClippingMaster.GCGBClippingID <= 0) //Add
            {
                masterDataTable = new GCGBClippingDTS.GCGBClippingMasterDataTable();
                masterRow = masterDataTable.NewGCGBClippingMasterRow();
            }
            else //Edit
            {
                if (!this.SaveUndo(gcgbClippingMaster.GCGBClippingID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(gcgbClippingMaster.GCGBClippingID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            masterRow.GCGBCommandID = this.gcgbClippingMaster.GCGBCommandID;

            masterRow.GCGBClippingDate = this.gcgbClippingMaster.GCGBClippingDate;
            masterRow.EntryDate = this.gcgbClippingMaster.EntryDate;

            masterRow.GCGBClippingReference = this.gcgbClippingMaster.GCGBClippingReference;
            masterRow.Description = this.gcgbClippingMaster.Description;

            masterRow.GCGBCommandTypeID = 1;

            masterRow.GCGBCommandReference = this.gcgbClippingMaster.GCGBCommandReference;
            masterRow.GCGBCommandDate = this.gcgbClippingMaster.GCGBCommandDate;

            masterRow.PreparedEmployeeID = this.gcgbClippingMaster.PreparedEmployeeID;
            masterRow.ApprovedEmployeeID = this.gcgbClippingMaster.ApprovedEmployeeID;

            masterRow.Remarks = this.gcgbClippingMaster.Remarks;

            masterRow.IsDraft = this.gcgbClippingMaster.IsDraft;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            masterRow.Approved = false;

            if (this.gcgbClippingMaster.GCGBClippingID <= 0) masterDataTable.AddGCGBClippingMasterRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            gcgbClippingID = masterRow.GCGBClippingID;

            return rowsAffected == 1;

        }

        private bool SaveDetail(int gcgbClippingID)
        {
            int serialID = 0; int rowsAffected = 0; int rowsPayment = 0;

            GCGBClippingDTS.GCGBClippingCollectionDataTable collectionDataTable = new GCGBClippingDTS.GCGBClippingCollectionDataTable();

            foreach (GCGBClippingCollection gcgbClippingCollection in this.gcgbClippingCollectionList)
            {
                if (gcgbClippingCollection.Quantity > 0)
                {
                    rowsPayment++;

                    GCGBClippingDTS.GCGBClippingCollectionRow collectionRow = collectionDataTable.NewGCGBClippingCollectionRow();

                    collectionRow.GCGBClippingID = gcgbClippingID;
                    collectionRow.SerialID = ++serialID;

                    collectionRow.GCGBClippingReference = this.gcgbClippingMaster.GCGBClippingReference;
                    collectionRow.GCGBClippingDate = this.gcgbClippingMaster.GCGBClippingDate;

                    collectionRow.GCGBCommandID = gcgbClippingCollection.GCGBCommandID;
                    collectionRow.GCGBCommandSerialID = gcgbClippingCollection.GCGBCommandSerialID;

                    collectionRow.GCGBCommandTypeID = 1;

                    collectionRow.WorkerNameID = gcgbClippingCollection.WorkerNameID;

                    collectionRow.MaterialSetID = gcgbClippingCollection.MaterialSetID;

                    collectionRow.Quantity = gcgbClippingCollection.Quantity;
                    collectionRow.QuantityLoss = gcgbClippingCollection.QuantityLoss;

                    collectionRow.MaterialSemiID1 = gcgbClippingCollection.MaterialSemiID1;
                    collectionRow.QuantityPerSet1 = gcgbClippingCollection.QuantityPerSet1;
                    collectionRow.QuantitySemi1 = 0;
                    collectionRow.QuantitySemiBad1 = gcgbClippingCollection.QuantitySemiBad1;
                    collectionRow.QuantitySemiLoss1 = gcgbClippingCollection.QuantitySemiLoss1;
                    collectionRow.QuantitySemiPreviousMinus1 = gcgbClippingCollection.QuantitySemiPreviousMinus1;

                    collectionRow.MaterialSemiID2 = gcgbClippingCollection.MaterialSemiID2;
                    collectionRow.QuantityPerSet2 = gcgbClippingCollection.QuantityPerSet2;
                    collectionRow.QuantitySemi2 = 0;
                    collectionRow.QuantitySemiBad2 = gcgbClippingCollection.QuantitySemiBad2;
                    collectionRow.QuantitySemiLoss2 = gcgbClippingCollection.QuantitySemiLoss2;
                    collectionRow.QuantitySemiPreviousMinus2 = gcgbClippingCollection.QuantitySemiPreviousMinus2;

                    collectionRow.MaterialSemiID3 = gcgbClippingCollection.MaterialSemiID3;
                    collectionRow.QuantityPerSet3 = gcgbClippingCollection.QuantityPerSet3;
                    collectionRow.QuantitySemi3 = 0;
                    collectionRow.QuantitySemiBad3 = gcgbClippingCollection.QuantitySemiBad3;
                    collectionRow.QuantitySemiLoss3 = gcgbClippingCollection.QuantitySemiLoss3;
                    collectionRow.QuantitySemiPreviousMinus3 = gcgbClippingCollection.QuantitySemiPreviousMinus3;


                    collectionRow.Remarks = gcgbClippingCollection.Remarks;

                    collectionRow.Approved = false;
                    collectionRow.InActive = false;

                    collectionDataTable.AddGCGBClippingCollectionRow(collectionRow);
                }
            }

            rowsAffected = this.CollectionTableAdapter.Update(collectionDataTable);
            if (rowsAffected != rowsPayment) throw new System.ArgumentException("Insufficient save", "Save detail");


            int rowsUpdated = this.CollectionTableAdapter.GCGBClippingSaveUpdate(gcgbClippingID, (int)GlobalMsADO.UpdateOption.SaveUpdate);
            if (rowsUpdated > rowsPayment) throw new System.ArgumentException("Insufficient save", "Save update");


        #endregion <Item Category>


            return true;
        }


        private bool SaveUndo(int gcgbClippingID)
        {
            int rowsUpdated = this.CollectionTableAdapter.GCGBClippingSaveUpdate(gcgbClippingID, (int)GlobalMsADO.UpdateOption.SaveUndo);
            int rowsDeleted = this.CollectionTableAdapter.Delete(gcgbClippingID);

            if (rowsUpdated > rowsDeleted) throw new System.ArgumentException("Insufficient save", "Save undo");

            return true;
        }


        private bool SaveConflict()
        {
            return GlobalDataObjectAction.SaveConflict("GCGBClippingSaveConflict", this.GCGBClippingID);
        }



        #region Import Excel

        public bool ImportGCGBClipping(string fileName)
        {
            try
            {
                if (this.ReadOnly) throw new System.ArgumentException("Import", "Permission conflict"); // || !this.IsDirty

                double paymentAmount = 0;
                ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

                DataTable excelDataTable = OleDbDatabase.OpenExcelSheet(OleDbDatabase.MappingTaskID.GCGBClipping, fileName);
                if (excelDataTable != null && excelDataTable.Rows.Count > 0)
                {
                    //Step 1: Check all rows to validate imported data
                    foreach (DataRow excelDataRow in excelDataTable.Rows)
                    {
                        if (excelDataRow["CustomerID"].ToString() != "" && double.TryParse(excelDataRow["PaymentAmount"].ToString(), out paymentAmount))
                        {
                            if (paymentAmount > 0)
                            {
                                GCGBClippingCollection gcgbClippingCollection = this.GCGBClippingCollectionList.FindItem(TypeDescriptor.CreateProperty(typeof(GCGBClippingCollection), "CustomerCode", typeof(string)), excelDataRow["CustomerID"].ToString().Trim());
                                if (gcgbClippingCollection == null || gcgbClippingCollection.RemainsQuantity < Math.Round(gcgbClippingCollection.Quantity + paymentAmount, GlobalVariables.Round0Quantity))
                                    exceptionTable.AddException(new string[] { gcgbClippingCollection == null ? "Customer not found" : "Out of balance", excelDataRow["CustomerID"].ToString() + " [" + paymentAmount.ToString("N0") + "]" });
                            }
                        }
                        else
                            exceptionTable.AddException(new string[] { "Invalid customer code or payment amount value", excelDataRow["CustomerID"].ToString() + " [" + excelDataRow["PaymentAmount"].ToString() + "]" });
                    }

                    //Step 2: Start import
                    if (exceptionTable.Table.Rows.Count <= 0)
                    {
                        this.GCGBClippingCollectionList.RaiseListChangedEvents = false;

                        foreach (DataRow excelDataRow in excelDataTable.Rows)
                        {
                            if (excelDataRow["CustomerID"].ToString() != "" && double.TryParse(excelDataRow["PaymentAmount"].ToString(), out paymentAmount))
                            {
                                if (paymentAmount > 0)
                                {
                                    GCGBClippingCollection gcgbClippingCollection = this.GCGBClippingCollectionList.FindItem(TypeDescriptor.CreateProperty(typeof(GCGBClippingCollection), "CustomerCode", typeof(string)), excelDataRow["CustomerID"].ToString().Trim());
                                    if (gcgbClippingCollection == null || gcgbClippingCollection.RemainsQuantity < Math.Round(gcgbClippingCollection.Quantity + paymentAmount, GlobalVariables.Round0Quantity))
                                        exceptionTable.AddException(new string[] { gcgbClippingCollection == null ? "Customer not found" : "Out of balance", excelDataRow["CustomerID"].ToString() + " [" + paymentAmount.ToString("N0") + "]" });
                                    else
                                    {
                                        gcgbClippingCollection.Quantity = gcgbClippingCollection.Quantity + paymentAmount;
                                        gcgbClippingCollection.Remarks = excelDataRow["Remarks"].ToString();
                                    }
                                }
                            }
                            else
                                exceptionTable.AddException(new string[] { "Invalid customer code or payment amount value", excelDataRow["CustomerID"].ToString() + " [" + excelDataRow["PaymentAmount"].ToString() + "]" });
                        }

                        this.GCGBClippingCollectionList.RaiseListChangedEvents = true;
                        this.GCGBClippingCollectionList.ResetBindings();
                    }

                    if (exceptionTable.Table.Rows.Count > 0)
                        throw new CustomException("Invalid data value or data entry not found", exceptionTable.Table);
                }
                return true;
            }
            catch (System.Exception exception)
            {
                this.GCGBClippingCollectionList.RaiseListChangedEvents = true;
                throw exception;
            }
        }

        #endregion Import Excel  


        private void RestoreProcedure()
        {

            string queryString;
            string[] queryArray;


            queryString = "     @LowerFillterDate DateTime, @UpperFillterDate DateTime " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          LEFT(DATENAME(Month, GCGBClippingMaster.GCGBClippingDate), 3) + ', ' + DATENAME(Year, GCGBClippingMaster.GCGBClippingDate) AS Month, * " + "\r\n";
            queryString = queryString + "       FROM            GCGBClippingMaster " + "\r\n";
            queryString = queryString + "       WHERE           GCGBClippingMaster.GCGBClippingDate >= @LowerFillterDate AND GCGBClippingMaster.GCGBClippingDate <= @UpperFillterDate " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBClippingListing", queryString);



            queryString = " @GCGBClippingID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          GCGBClippingMaster.GCGBClippingID, GCGBClippingMaster.GCGBClippingDate, GCGBClippingMaster.GCGBClippingReference, GCGBClippingMaster.GCGBCommandID, GCGBCommandMaster.GCGBCommandReference, GCGBCommandMaster.GCGBCommandDate, GCGBClippingMaster.GCGBCommandTypeID, GCGBClippingMaster.EntryDate, GCGBClippingMaster.PreparedEmployeeID, GCGBClippingMaster.ApprovedEmployeeID, GCGBClippingMaster.Description, GCGBClippingMaster.Remarks, GCGBClippingMaster.UserID, GCGBClippingMaster.UserOrganizationID, GCGBClippingMaster.Approved, GCGBClippingMaster.IsDraft " + "\r\n";
            queryString = queryString + "       FROM            GCGBClippingMaster INNER JOIN GCGBCommandMaster ON GCGBClippingMaster.GCGBCommandID = GCGBCommandMaster.GCGBCommandID " + "\r\n";
            queryString = queryString + "       WHERE           GCGBClippingID = @GCGBClippingID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBClippingMasterSelect", queryString);


            queryString = " @GCGBClippingDate datetime, @GCGBClippingReference nvarchar(50), @GCGBCommandID int, @GCGBCommandTypeID int, @EntryDate datetime, @PreparedEmployeeID int, @ApprovedEmployeeID int, @Description nvarchar(150), @Remarks nvarchar(100), @UserID int, @UserOrganizationID int, @Approved bit, @IsDraft bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO     GCGBClippingMaster (GCGBClippingDate, GCGBClippingReference, GCGBCommandID, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Description, Remarks, UserID, UserOrganizationID, Approved, IsDraft) VALUES (@GCGBClippingDate, @GCGBClippingReference, @GCGBCommandID, @GCGBCommandTypeID, @EntryDate, @PreparedEmployeeID, @ApprovedEmployeeID, @Description, @Remarks, @UserID, @UserOrganizationID, @Approved, @IsDraft) " + "\r\n";
            queryString = queryString + "       SELECT          GCGBClippingID, GCGBClippingDate, GCGBClippingReference, GCGBCommandID, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Description, Remarks, UserID, UserOrganizationID, Approved FROM GCGBClippingMaster WHERE (GCGBClippingID = SCOPE_IDENTITY()) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBClippingMasterInsert", queryString);


            queryString = " @GCGBClippingID int, @GCGBClippingDate datetime, @GCGBClippingReference nvarchar(50), @GCGBCommandID int, @GCGBCommandTypeID int, @EntryDate datetime, @PreparedEmployeeID int, @ApprovedEmployeeID int, @Description nvarchar(150), @Remarks nvarchar(100), @UserID int, @UserOrganizationID int, @Approved bit, @IsDraft bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          GCGBClippingMaster SET GCGBClippingDate = @GCGBClippingDate, GCGBClippingReference = @GCGBClippingReference, GCGBCommandID = @GCGBCommandID, GCGBCommandTypeID = @GCGBCommandTypeID, EntryDate = @EntryDate, PreparedEmployeeID = @PreparedEmployeeID, ApprovedEmployeeID = @ApprovedEmployeeID, Description = @Description, Remarks = @Remarks, UserID = @UserID, UserOrganizationID = @UserOrganizationID, Approved = @Approved, IsDraft = @IsDraft WHERE GCGBClippingID = @GCGBClippingID " + "\r\n";
            queryString = queryString + "       SELECT          GCGBClippingID, GCGBClippingDate, GCGBClippingReference, GCGBCommandID, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Description, Remarks, UserID, UserOrganizationID, Approved FROM GCGBClippingMaster WHERE (GCGBClippingID = @GCGBClippingID) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBClippingMasterUpdate", queryString);


            queryString = " @GCGBClippingID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM     [GCGBClippingMaster] WHERE GCGBClippingID = @GCGBClippingID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBClippingMasterDelete", queryString);




            queryString = " @GCGBClippingID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          GCGBClippingID, SerialID, GCGBClippingReference, GCGBClippingDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID, MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1, QuantitySemiLoss1, QuantitySemiPreviousMinus1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2, QuantitySemiLoss2, QuantitySemiPreviousMinus2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3, QuantitySemiLoss3, QuantitySemiPreviousMinus3, Remarks, Approved, InActive " + "\r\n";
            queryString = queryString + "       FROM            GCGBClippingCollection " + "\r\n";
            queryString = queryString + "       WHERE           GCGBClippingID = @GCGBClippingID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBClippingCollectionSelect", queryString);


            queryString = " @GCGBClippingID int, @SerialID int, @GCGBClippingReference nvarchar(50), @GCGBClippingDate datetime, @GCGBCommandID int, @GCGBCommandSerialID int, @GCGBCommandTypeID int, @WorkerNameID int, @MaterialSetID int, @Quantity float, @QuantityLoss float, @MaterialSemiID1 int, @QuantityPerSet1 float, @QuantitySemi1 float, @QuantitySemiBad1 float, @QuantitySemiLoss1 float, @QuantitySemiPreviousMinus1 float, @MaterialSemiID2 int, @QuantityPerSet2 float, @QuantitySemi2 float, @QuantitySemiBad2 float, @QuantitySemiLoss2 float, @QuantitySemiPreviousMinus2 float, @MaterialSemiID3 int, @QuantityPerSet3 float, @QuantitySemi3 float, @QuantitySemiBad3 float, @QuantitySemiLoss3 float, @QuantitySemiPreviousMinus3 float, @Remarks nvarchar(150), @Approved bit, @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO     GCGBClippingCollection (GCGBClippingID, SerialID, GCGBClippingReference, GCGBClippingDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID, MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1,  QuantitySemiLoss1, QuantitySemiPreviousMinus1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2,  QuantitySemiLoss2, QuantitySemiPreviousMinus2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3,  QuantitySemiLoss3, QuantitySemiPreviousMinus3, Remarks, Approved, InActive) " + "\r\n";
            queryString = queryString + "                   VALUES (@GCGBClippingID, @SerialID, @GCGBClippingReference, @GCGBClippingDate, @GCGBCommandID, @GCGBCommandSerialID, @GCGBCommandTypeID, @WorkerNameID, @MaterialSetID, @Quantity, @QuantityLoss, @MaterialSemiID1, @QuantityPerSet1, @QuantitySemi1, @QuantitySemiBad1, @QuantitySemiLoss1, @QuantitySemiPreviousMinus1, @MaterialSemiID2, @QuantityPerSet2, @QuantitySemi2, @QuantitySemiBad2, @QuantitySemiLoss2, @QuantitySemiPreviousMinus2, @MaterialSemiID3, @QuantityPerSet3, @QuantitySemi3, @QuantitySemiBad3, @QuantitySemiLoss3, @QuantitySemiPreviousMinus3, @Remarks, @Approved, @InActive) " + "\r\n";
            queryString = queryString + "       SELECT          GCGBClippingID, SerialID, GCGBClippingReference, GCGBClippingDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID, MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1, QuantitySemiLoss1, QuantitySemiPreviousMinus1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2, QuantitySemiLoss2, QuantitySemiPreviousMinus2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3, QuantitySemiLoss3, QuantitySemiPreviousMinus3, Remarks, Approved, InActive FROM GCGBClippingCollection WHERE (GCGBClippingID = @GCGBClippingID) AND (SerialID = @SerialID) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBClippingCollectionInsert", queryString);


            queryString = " @GCGBClippingID int, @SerialID int, @GCGBClippingReference nvarchar(50), @GCGBClippingDate datetime, @GCGBCommandID int, @GCGBCommandSerialID int, @GCGBCommandTypeID int, @WorkerNameID int, @MaterialSetID int, @Quantity float, @QuantityLoss float, @MaterialSemiID1 int, @QuantityPerSet1 float, @QuantitySemi1 float, @QuantitySemiBad1 float, @QuantitySemiLoss1 float, @QuantitySemiPreviousMinus1 float, @MaterialSemiID2 int, @QuantityPerSet2 float, @QuantitySemi2 float, @QuantitySemiBad2 float, @QuantitySemiLoss2 float, @QuantitySemiPreviousMinus2 float, @MaterialSemiID3 int, @QuantityPerSet3 float, @QuantitySemi3 float, @QuantitySemiBad3 float, @QuantitySemiLoss3 float, @QuantitySemiPreviousMinus3 float, @Remarks nvarchar(150), @Approved bit, @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          GCGBClippingCollection SET GCGBClippingReference = @GCGBClippingReference, GCGBClippingDate = @GCGBClippingDate, GCGBCommandID = @GCGBCommandID, GCGBCommandSerialID = @GCGBCommandSerialID, GCGBCommandTypeID = @GCGBCommandTypeID, WorkerNameID = @WorkerNameID, MaterialSetID = @MaterialSetID, Quantity = @Quantity, QuantityLoss = @QuantityLoss, MaterialSemiID1 = @MaterialSemiID1, QuantityPerSet1 = @QuantityPerSet1, QuantitySemi1 = @QuantitySemi1, QuantitySemiBad1 = @QuantitySemiBad1,  QuantitySemiLoss1 = @QuantitySemiLoss1, QuantitySemiPreviousMinus1 = @QuantitySemiPreviousMinus1, " + "\r\n";
            queryString = queryString + "                 MaterialSemiID2 = @MaterialSemiID2, QuantityPerSet2 = @QuantityPerSet2, QuantitySemi2 = @QuantitySemi2, QuantitySemiBad2 = @QuantitySemiBad2, QuantitySemiLoss2 = @QuantitySemiLoss2, QuantitySemiPreviousMinus2 = @QuantitySemiPreviousMinus2, MaterialSemiID3 = @MaterialSemiID3, QuantityPerSet3 = @QuantityPerSet3, QuantitySemi3 = @QuantitySemi3, QuantitySemiBad3 = @QuantitySemiBad3, QuantitySemiLoss3 = @QuantitySemiLoss3, QuantitySemiPreviousMinus3 = @QuantitySemiPreviousMinus3, Remarks = @Remarks, Approved = @Approved, InActive = @InActive WHERE (GCGBClippingID = @GCGBClippingID) AND (SerialID = @SerialID) " + "\r\n";
            queryString = queryString + "       SELECT          GCGBClippingID, SerialID, GCGBClippingReference, GCGBClippingDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID, MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1, QuantitySemiLoss1, QuantitySemiPreviousMinus1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2, QuantitySemiLoss2, QuantitySemiPreviousMinus2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3, QuantitySemiLoss3, QuantitySemiPreviousMinus3, Remarks, Approved, InActive FROM GCGBClippingCollection WHERE (GCGBClippingID = @GCGBClippingID) AND (SerialID = @SerialID) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBClippingCollectionUpdate", queryString);

            queryString = " @GCGBClippingID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM     GCGBClippingCollection WHERE GCGBClippingID = @GCGBClippingID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBClippingCollectionDelete", queryString);


            string queryEdit; string queryNew;

            queryEdit = "               SELECT          GCGBCommandCollection.GCGBCommandID, GCGBCommandCollection.SerialID AS GCGBCommandSerialID, GCGBCommandCollection.GCGBCommandReference, GCGBCommandCollection.GCGBCommandDate, GCGBClippingCollection.WorkerNameID, GCGBCommandCollection.MaterialSetID, ROUND(GCGBCommandCollection.Quantity * @IsDraft + GCGBCommandCollection.QuantityStaple * (1 - @IsDraft) - GCGBCommandCollection.QuantityClipping - GCGBCommandCollection.QuantityClippingLoss + GCGBClippingCollection.Quantity + GCGBClippingCollection.QuantityLoss, " + GlobalVariables.Round0Amount.ToString() + ") AS RemainsQuantity, GCGBClippingCollection.Quantity, GCGBClippingCollection.QuantityLoss, " + "\r\n";
            queryEdit = queryEdit + "                   GCGBCommandCollection.MaterialSemiID1, GCGBCommandCollection.QuantityPerSet1, GCGBClippingCollection.QuantitySemiBad1, GCGBClippingCollection.QuantitySemiLoss1, GCGBClippingCollection.QuantitySemiPreviousMinus1, GCGBCommandCollection.MaterialSemiID2, GCGBCommandCollection.QuantityPerSet2, GCGBClippingCollection.QuantitySemiBad2, GCGBClippingCollection.QuantitySemiLoss2, GCGBClippingCollection.QuantitySemiPreviousMinus2, GCGBCommandCollection.MaterialSemiID3, GCGBCommandCollection.QuantityPerSet3, GCGBClippingCollection.QuantitySemiBad3, GCGBClippingCollection.QuantitySemiLoss3, GCGBClippingCollection.QuantitySemiPreviousMinus3, GCGBClippingCollection.Remarks, 0 AS RSC " + "\r\n";
            queryEdit = queryEdit + "   FROM            GCGBCommandCollection INNER JOIN " + "\r\n";
            queryEdit = queryEdit + "                   GCGBClippingCollection ON GCGBCommandCollection.GCGBCommandID = GCGBClippingCollection.GCGBCommandID AND GCGBCommandCollection.SerialID = GCGBClippingCollection.GCGBCommandSerialID " + "\r\n";
            queryEdit = queryEdit + "   WHERE           (@GCGBCommandID <= 0 OR @GCGBCommandID = GCGBCommandCollection.GCGBCommandID) AND GCGBClippingCollection.GCGBClippingID = @GCGBClippingID " + "\r\n";

            queryNew = "                SELECT          GCGBCommandCollection.GCGBCommandID, GCGBCommandCollection.SerialID AS GCGBCommandSerialID, GCGBCommandCollection.GCGBCommandReference, GCGBCommandCollection.GCGBCommandDate, 0 AS WorkerNameID, GCGBCommandCollection.MaterialSetID, ROUND(GCGBCommandCollection.Quantity * @IsDraft + GCGBCommandCollection.QuantityStaple * (1 - @IsDraft) - GCGBCommandCollection.QuantityClipping - GCGBCommandCollection.QuantityClippingLoss, " + GlobalVariables.Round0Amount.ToString() + ") AS RemainsQuantity, 0 AS Quantity, 0 AS QuantityLoss, " + "\r\n";
            queryNew = queryNew + "                     GCGBCommandCollection.MaterialSemiID1, GCGBCommandCollection.QuantityPerSet1, 0 AS QuantitySemiBad1, 0 AS QuantitySemiLoss1, 0 AS QuantitySemiPreviousMinus1, GCGBCommandCollection.MaterialSemiID2, GCGBCommandCollection.QuantityPerSet2, 0 AS QuantitySemiBad2, 0 AS QuantitySemiLoss2, 0 AS QuantitySemiPreviousMinus2, GCGBCommandCollection.MaterialSemiID3, GCGBCommandCollection.QuantityPerSet3, 0 AS QuantitySemiBad3, 0 AS QuantitySemiLoss3, 0 AS QuantitySemiPreviousMinus3, '' AS Remarks, 1 AS RSC " + "\r\n";
            queryNew = queryNew + "     FROM            GCGBCommandCollection " + "\r\n";
            queryNew = queryNew + "     WHERE           (@GCGBCommandID <= 0 OR @GCGBCommandID = GCGBCommandCollection.GCGBCommandID) AND GCGBCommandCollection.Approved = 1 AND ROUND(GCGBCommandCollection.Quantity * @IsDraft + GCGBCommandCollection.QuantityStaple * (1 - @IsDraft) - GCGBCommandCollection.QuantityClipping - GCGBCommandCollection.QuantityClippingLoss, " + GlobalVariables.Round0Amount.ToString() + ") > 0 " + "\r\n";

            queryString = " @GCGBClippingID Int, @GCGBCommandID Int, @ReadonlyMode bit, @IsDraft bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + " IF (@GCGBClippingID <= 0) " + "\r\n";
            queryString = queryString + "    BEGIN " + "\r\n";
            queryString = queryString + "       " + queryNew + "\r\n";
            queryString = queryString + "        ORDER BY GCGBCommandCollection.GCGBCommandDate, GCGBCommandCollection.GCGBCommandID, GCGBCommandSerialID " + "\r\n";
            queryString = queryString + "    END " + "\r\n";
            queryString = queryString + " ELSE " + "\r\n";

            queryString = queryString + "   IF (@ReadonlyMode = 1) " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           " + queryEdit + "\r\n";
            queryString = queryString + "           ORDER BY GCGBCommandCollection.GCGBCommandDate, GCGBCommandCollection.GCGBCommandID, GCGBCommandSerialID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";
            queryString = queryString + "   ELSE " + "\r\n"; //FULL SELECT FOR EDIT MODE

            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           " + queryEdit + "\r\n";
            queryString = queryString + "           UNION ALL " + "\r\n";
            queryString = queryString + "           " + queryNew + "\r\n";
            queryString = queryString + "           ORDER BY GCGBCommandCollection.GCGBCommandDate, GCGBCommandCollection.GCGBCommandID, GCGBCommandSerialID, RSC " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBClippingGetDetail", queryString);



            queryString = " @GCGBClippingID int, @UpdateOption int " + "\r\n"; //UpdateOption: 1 Savedetail, -1:Saveundo
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          GCGBCommandCollection " + "\r\n";
            queryString = queryString + "       SET             GCGBCommandCollection.QuantityClipping = ROUND(GCGBCommandCollection.QuantityClipping + GCGBClippingCollection.Quantity * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantityClippingLoss = ROUND(GCGBCommandCollection.QuantityClippingLoss + GCGBClippingCollection.QuantityLoss * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), " + "\r\n";
            queryString = queryString + "                  GCGBCommandCollection.QuantitySemiClippingBad1 = ROUND(GCGBCommandCollection.QuantitySemiClippingBad1 + GCGBClippingCollection.QuantitySemiBad1 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiClippingLoss1 = ROUND(GCGBCommandCollection.QuantitySemiClippingLoss1 + GCGBClippingCollection.QuantitySemiLoss1 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiClippingPreviousMinus1 = ROUND(GCGBCommandCollection.QuantitySemiClippingPreviousMinus1 + GCGBClippingCollection.QuantitySemiPreviousMinus1 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), " + "\r\n";
            queryString = queryString + "                  GCGBCommandCollection.QuantitySemiClippingBad2 = ROUND(GCGBCommandCollection.QuantitySemiClippingBad2 + GCGBClippingCollection.QuantitySemiBad2 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiClippingLoss2 = ROUND(GCGBCommandCollection.QuantitySemiClippingLoss2 + GCGBClippingCollection.QuantitySemiLoss2 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiClippingPreviousMinus2 = ROUND(GCGBCommandCollection.QuantitySemiClippingPreviousMinus2 + GCGBClippingCollection.QuantitySemiPreviousMinus2 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + ")," + "\r\n";
            queryString = queryString + "                  GCGBCommandCollection.QuantitySemiClippingBad3 = ROUND(GCGBCommandCollection.QuantitySemiClippingBad3 + GCGBClippingCollection.QuantitySemiBad3 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiClippingLoss3 = ROUND(GCGBCommandCollection.QuantitySemiClippingLoss3 + GCGBClippingCollection.QuantitySemiLoss3 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiClippingPreviousMinus3 = ROUND(GCGBCommandCollection.QuantitySemiClippingPreviousMinus3 + GCGBClippingCollection.QuantitySemiPreviousMinus3 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + ")" + "\r\n";
            queryString = queryString + "       FROM           (SELECT GCGBCommandID, GCGBCommandSerialID, SUM(Quantity) AS Quantity, SUM(QuantityLoss) As QuantityLoss, SUM(QuantitySemiBad1) AS QuantitySemiBad1, SUM(QuantitySemiLoss1) AS QuantitySemiLoss1, SUM(QuantitySemiPreviousMinus1) AS QuantitySemiPreviousMinus1, SUM(QuantitySemiBad2) AS QuantitySemiBad2, SUM(QuantitySemiLoss2) AS QuantitySemiLoss2, SUM(QuantitySemiPreviousMinus2) AS QuantitySemiPreviousMinus2, SUM(QuantitySemiBad3) AS QuantitySemiBad3, SUM(QuantitySemiLoss3) AS QuantitySemiLoss3, SUM(QuantitySemiPreviousMinus3) AS QuantitySemiPreviousMinus3  FROM GCGBClippingCollection WHERE GCGBClippingID = @GCGBClippingID GROUP BY GCGBCommandID, GCGBCommandSerialID) GCGBClippingCollection INNER JOIN " + "\r\n";
            queryString = queryString + "                       GCGBCommandCollection ON GCGBClippingCollection.GCGBCommandID = GCGBCommandCollection.GCGBCommandID AND GCGBClippingCollection.GCGBCommandSerialID = GCGBCommandCollection.SerialID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBClippingSaveUpdate", queryString);




            /// <summary>
            /// Check for save conflict
            /// </summary>
            queryArray = new string[1];
            queryArray[0] = " SELECT TOP 1  GCGBClippingMaster.GCGBClippingID FROM GCGBClippingMaster INNER JOIN GCGBCommandMaster ON GCGBClippingMaster.GCGBCommandID = GCGBCommandMaster.GCGBCommandID AND GCGBClippingMaster.GCGBClippingDate < GCGBCommandMaster.GCGBCommandDate ";            
            //queryArray[1] = " SELECT TOP 1  GCGBCommandID FROM GCGBCommandCollection WHERE (ROUND(Quantity * " + this.GCGBClippingMaster.IsDraft + " + QuantityStaple * (1 - " + this.GCGBClippingMaster.IsDraft + ") - QuantityClipping - QuantityClippingLoss, " + GlobalVariables.Round0Amount.ToString() + ") < 0) ";

            SQLDatabase.CreateProcedureToCheckExisting("GCGBClippingSaveConflict", queryArray);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[1];
            queryArray[0] = " SELECT TOP 1 GCGBClippingID FROM GCGBClippingMaster WHERE GCGBClippingID = @FindIdentityID AND Approved = 1 ";

            SQLDatabase.CreateProcedureToCheckExisting("GCGBClippingApproved", queryArray);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[0];

            SQLDatabase.CreateProcedureToCheckExisting("GCGBClippingEditable");// queryArray);


        }

    }

}

